package pub.cleangao;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.text.DecimalFormat;
import java.util.*;

public class ReadFinance {
    public static Workbook loadFromFile(String filePath) throws Exception {
        String extend = filePath.substring(filePath.lastIndexOf("."));
        if (extend.equalsIgnoreCase(".xls")) {
            return new HSSFWorkbook(new FileInputStream(filePath));
        } else if (extend.equalsIgnoreCase(".xlsx")) {
            return new XSSFWorkbook(new FileInputStream(filePath));
        }
        return null;
    }

    public static Map<String, BigDecimal> readOriginalFromSheet(String filePath) throws Exception {
        Workbook workbook = loadFromFile(filePath);
        int sheetCount = workbook.getNumberOfSheets();
        int sheetIndex = 0;
        int keyIndex = 6;
        int valueIndex = 12;
        int skipRowNum = 1;
        if (sheetIndex >= sheetCount) {
            return null;
        }
        Sheet sheet = workbook.getSheetAt(sheetIndex);
        int rowNum = sheet.getPhysicalNumberOfRows();
        if (skipRowNum >= rowNum) {
            return null;
        }
        System.out.println("文件:" + filePath + " 第 " + (sheetIndex + 1) + "个表，总共 " + rowNum + " 行");
        Map<String, BigDecimal> rst = new HashMap<>(rowNum);
        for (int i = skipRowNum; i < rowNum; i++) {
            Row row = sheet.getRow(i);
            if (row == null) {
                continue;
            }
            Cell keyCell = row.getCell(keyIndex);
            if (keyCell == null) {
                continue;
            }
            Cell valueCell = row.getCell(valueIndex);
            String key = convertCellValueToString(keyCell);
            key = key.substring(1, "TC20250522lIS9m2".length() + 1);
            String valStr = convertCellValueToString(valueCell);
            valStr = valStr.substring(1);
            BigDecimal value = new BigDecimal(valStr);
            value.setScale(2, RoundingMode.HALF_UP);
            rst.put(key.trim(), value);
        }
        return rst;
    }

    public static Map<String, BigDecimal> readFinanceFromSheet(String filePath) throws Exception {
        Workbook workbook = loadFromFile(filePath);
        int sheetCount = workbook.getNumberOfSheets();
        int sheetIndex = 0;
        int keyIndex = 1;
        int valueIndex = 5;
        int skipRowNum = 5;
        if (sheetIndex >= sheetCount) {
            return null;
        }
        Sheet sheet = workbook.getSheetAt(sheetIndex);
        int rowNum = sheet.getPhysicalNumberOfRows();
        if (skipRowNum >= rowNum) {
            return null;
        }
        System.out.println("文件:" + filePath + " 第 " + (sheetIndex + 1) + "个表，总共 " + rowNum + " 行");
        Map<String, BigDecimal> rst = new HashMap<>(rowNum);
        for (int i = skipRowNum; i < rowNum; i++) {
            Row row = sheet.getRow(i);
            if (row == null) {
                continue;
            }
            Cell keyCell = row.getCell(keyIndex);
            if (keyCell == null) {
                continue;
            }
            Cell valueCell = row.getCell(valueIndex);
            String key = convertCellValueToString(keyCell);
            String valStr = convertCellValueToString(valueCell);
            BigDecimal value = new BigDecimal(valStr);
            value.setScale(2, RoundingMode.HALF_UP);
            rst.put(key.trim(), value);
        }
        return rst;
    }

    public static void deleteFinanceRow(Set<String> ids, String filePath) throws Exception {
        Workbook workbook = loadFromFile(filePath);
        int sheetCount = workbook.getNumberOfSheets();
        int sheetIndex = 0;
        int keyIndex = 1;
        int valueIndex = 5;
        int skipRowNum = 5;
        if (sheetIndex >= sheetCount) {
            return;
        }
        Sheet sheet = workbook.getSheetAt(sheetIndex);
        int rowNum = sheet.getPhysicalNumberOfRows();
        if (skipRowNum >= rowNum) {
            return;
        }
        System.out.println("文件:" + filePath + " 第 " + (sheetIndex + 1) + "个表，总共 " + rowNum + " 行");
        Map<String, BigDecimal> rst = new HashMap<>(rowNum);
        for (int i = skipRowNum; i < rowNum; i++) {
            Row row = sheet.getRow(i);
            if (row == null) {
                continue;
            }
            Cell keyCell = row.getCell(keyIndex);
            if (keyCell == null) {
                continue;
            }
            String key = convertCellValueToString(keyCell);
            if (ids.contains(key.trim())) {
                sheet.removeRow(row);
            }
        }
        FileOutputStream os = new FileOutputStream(filePath+"_2.xlsx");
        workbook.write(os);
        os.close();
        return;
    }

    public static void deleteOriginalRow(Set<String> ids, String filePath) throws Exception {
        Workbook workbook = loadFromFile(filePath);
        int sheetCount = workbook.getNumberOfSheets();
        int sheetIndex = 0;
        int keyIndex = 6;
        int valueIndex = 12;
        int skipRowNum = 1;
        if (sheetIndex >= sheetCount) {
            return;
        }
        Sheet sheet = workbook.getSheetAt(sheetIndex);
        int rowNum = sheet.getPhysicalNumberOfRows();
        if (skipRowNum >= rowNum) {
            return;
        }
        System.out.println("文件:" + filePath + " 第 " + (sheetIndex + 1) + "个表，总共 " + rowNum + " 行");
        Map<String, BigDecimal> rst = new HashMap<>(rowNum);
        for (int i = skipRowNum; i < rowNum; i++) {
            Row row = sheet.getRow(i);
            if (row == null) {
                continue;
            }
            Cell keyCell = row.getCell(keyIndex);
            if (keyCell == null) {
                continue;
            }
            String key = convertCellValueToString(keyCell);
            key = key.substring(1, "TC20250522lIS9m2".length() + 1);
            if (ids.contains(key.trim())) {
                sheet.removeRow(row);
            }
        }
        FileOutputStream os = new FileOutputStream(filePath+"_2.xlsx");
        workbook.write(os);
        os.close();

        return;
    }


    private static String convertCellValueToString(Cell cell) {
        if (cell == null) {
            return null;
        }
        String returnValue = null;
        switch (cell.getCellType()) {
            case NUMERIC:   //数字
                Double doubleValue = cell.getNumericCellValue();

                // 格式化科学计数法，取一位整数
                DecimalFormat df = new DecimalFormat("0");
                returnValue = df.format(doubleValue);
                break;
            case STRING:    //字符串
                returnValue = cell.getStringCellValue();
                break;
            case BOOLEAN:   //布尔
                Boolean booleanValue = cell.getBooleanCellValue();
                returnValue = booleanValue.toString();
                break;
            case BLANK:     // 空值
                break;
            case FORMULA:   // 公式
                returnValue = cell.getCellFormula();
                break;
            case ERROR:     // 故障
                break;
            default:
                break;
        }
        if (returnValue == null || returnValue.trim().equals("")) {
            return null;
        }
        return returnValue.trim();
    }

}
